package com.cqeec.myblog.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.cqeec.myblog.model.DataDictionary;
import com.cqeec.myblog.util.DbUtil;

public class DataDictionaryDaoImpl implements DataDictionaryDao {

	@Override
	public void delete(String id) throws ClassNotFoundException, SQLException {
		Connection conn = DbUtil.getDbConnection();
		Statement stat = conn.createStatement();
		stat.executeUpdate("DELETE FROM DataDictionary WHERE id='" + id + "'");
	}

	@Override
	public void update(DataDictionary dd) throws ClassNotFoundException, SQLException {
		Connection conn = DbUtil.getDbConnection();
		PreparedStatement ps = conn.prepareStatement("UPDATE DataDictionary SET name=?,type=? WHERE id=?");
		ps.setString(1, dd.getName());
		ps.setString(2, dd.getType());
		ps.setString(3, dd.getId());
		ps.executeUpdate();
	}

	@Override
	public void insert(String id, String name, String type) throws ClassNotFoundException, SQLException {
		Connection conn = DbUtil.getDbConnection();
		PreparedStatement ps = conn.prepareStatement("INSERT INTO DataDictionary VALUES(?,?,?)");
		ps.setString(1, id);
		ps.setString(2, name);
		ps.setString(3, type);
		ps.executeUpdate();
	}

	@Override
	public void insert(DataDictionary dd) throws ClassNotFoundException, SQLException {
		Connection conn = DbUtil.getDbConnection();
		PreparedStatement ps = conn.prepareStatement("INSERT INTO DataDictionary VALUES(?,?,?)");
		ps.setString(1, dd.getId());
		ps.setString(2, dd.getName());
		ps.setString(3, dd.getType());
		ps.executeUpdate();
	}

	@Override
	public List<DataDictionary> selectByType(String type) throws ClassNotFoundException, SQLException {
		List<DataDictionary> list = new ArrayList<DataDictionary>();
		// 从数据库中查询数据封装为对应的数据字典对象
		Connection conn = DbUtil.getDbConnection();
		PreparedStatement ps = conn.prepareStatement("SELECT * FROM DataDictionary WHERE type=?");
		ps.setString(1, type);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			list.add(new DataDictionary(rs.getString("id"), rs.getString("name"), rs.getString("type")));
		}
		rs.close();
		ps.close();
		return list;
	}

	@Override
	public Map<String,Integer> selectBlogerArticleTypeAndTotal() throws ClassNotFoundException, SQLException {
		Map<String,Integer> map = new HashMap<String,Integer>();
		// 从数据库中查询数据封装为对应的数据字典对象
		Connection conn = DbUtil.getDbConnection();
		Statement state = conn.createStatement();
		ResultSet rs=	state.executeQuery("SELECT name,count(a.id) as total FROM DataDictionary dd LEFT JOIN Article a ON dd.id=a.type WHERE dd.type='ArticleType' GROUP BY name");
		while (rs.next()) {
			map.put(rs.getString("name"), rs.getInt("total"));
		}
		rs.close();
		state.close();
		return map;
	}

}
